package com.simplehire.repository;

import com.simplehire.entity.JobDescription;
import com.simplehire.entity.ResumeMatchResult;
import com.simplehire.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 简历匹配结果数据访问层
 */
@Repository
public interface ResumeMatchResultRepository extends JpaRepository<ResumeMatchResult, Long> {
    
    /**
     * 根据用户查找匹配结果
     */
    List<ResumeMatchResult> findByUserOrderByCreatedAtDesc(User user);
    
    /**
     * 根据岗位查找匹配结果
     */
    List<ResumeMatchResult> findByJobDescriptionOrderByOverallScoreDesc(JobDescription jobDescription);
    
    /**
     * 根据用户和岗位查找匹配结果
     */
    Optional<ResumeMatchResult> findByUserAndJobDescription(User user, JobDescription jobDescription);
    
    /**
     * 根据匹配等级查找结果
     */
    List<ResumeMatchResult> findByMatchLevelOrderByCreatedAtDesc(ResumeMatchResult.MatchLevel matchLevel);
    
    /**
     * 根据分数范围查找结果
     */
    List<ResumeMatchResult> findByOverallScoreBetweenOrderByOverallScoreDesc(Double minScore, Double maxScore);
    
    /**
     * 根据用户ID查找匹配结果（按创建时间倒序）
     */
    List<ResumeMatchResult> findByUserIdOrderByCreatedAtDesc(Long userId);
    
    /**
     * 根据ID和用户ID查找匹配结果
     */
    Optional<ResumeMatchResult> findByIdAndUserId(Long id, Long userId);
    
    /**
     * 获取用户最近的匹配结果
     */
    @Query("SELECT rmr FROM ResumeMatchResult rmr WHERE rmr.user = :user " +
           "ORDER BY rmr.createdAt DESC")
    List<ResumeMatchResult> findRecentMatchResults(@Param("user") User user);
    
    /**
     * 获取用户最高分的匹配结果
     */
    @Query("SELECT rmr FROM ResumeMatchResult rmr WHERE rmr.user = :user " +
           "ORDER BY rmr.overallScore DESC")
    List<ResumeMatchResult> findTopMatchResults(@Param("user") User user);
    
    /**
     * 获取岗位的最佳匹配候选人
     */
    @Query("SELECT rmr FROM ResumeMatchResult rmr WHERE rmr.jobDescription = :jobDescription " +
           "ORDER BY rmr.overallScore DESC")
    List<ResumeMatchResult> findBestCandidates(@Param("jobDescription") JobDescription jobDescription);
    
    /**
     * 根据时间范围查找匹配结果
     */
    List<ResumeMatchResult> findByCreatedAtBetweenOrderByCreatedAtDesc(LocalDateTime startDate, LocalDateTime endDate);
    
    /**
     * 统计用户的匹配次数
     */
    Long countByUser(User user);
    
    /**
     * 统计岗位的匹配次数
     */
    Long countByJobDescription(JobDescription jobDescription);
    
    /**
     * 获取用户的平均匹配分数
     */
    @Query("SELECT AVG(rmr.overallScore) FROM ResumeMatchResult rmr WHERE rmr.user = :user")
    Double getAverageScoreByUser(@Param("user") User user);
    
    /**
     * 获取岗位的平均匹配分数
     */
    @Query("SELECT AVG(rmr.overallScore) FROM ResumeMatchResult rmr WHERE rmr.jobDescription = :jobDescription")
    Double getAverageScoreByJob(@Param("jobDescription") JobDescription jobDescription);
    
    /**
     * 获取用户在特定分数以上的匹配结果
     */
    @Query("SELECT rmr FROM ResumeMatchResult rmr WHERE rmr.user = :user " +
           "AND rmr.overallScore >= :minScore ORDER BY rmr.overallScore DESC")
    List<ResumeMatchResult> findHighScoreMatches(@Param("user") User user, @Param("minScore") Double minScore);
    
    /**
     * 获取系统整体统计信息
     */
    @Query("SELECT COUNT(rmr), AVG(rmr.overallScore), MAX(rmr.overallScore), MIN(rmr.overallScore) " +
           "FROM ResumeMatchResult rmr")
    Object[] getSystemStats();
    
    /**
     * 根据匹配等级统计数量
     */
    @Query("SELECT rmr.matchLevel, COUNT(rmr) FROM ResumeMatchResult rmr GROUP BY rmr.matchLevel")
    List<Object[]> getMatchLevelStats();
    
    /**
     * 获取最受欢迎的技能（基于匹配结果）
     */
    @Query("SELECT skill, COUNT(skill) FROM ResumeMatchResult rmr " +
           "JOIN rmr.matchedSkills skill GROUP BY skill ORDER BY COUNT(skill) DESC")
    List<Object[]> getPopularSkills();
    
    /**
     * 删除指定时间之前的旧记录
     */
    void deleteByCreatedAtBefore(LocalDateTime cutoffDate);
}